package com.xj.demoExcel.easyexcel;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.util.FileUtils;
import com.alibaba.excel.write.merge.LoopMergeStrategy;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.WriteTable;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.net.MalformedURLException;
import java.net.URL;
import java.util.*;

/**
 * easyExcel一次只能处理一个sheet,如果该文件中有多个不同字段的sheet,则需要准备多个entity和listener
 * 默认处理第1个sheet，跳过第0行，从第1行开始处理
 */
public class Excel_EasyExcel {

    public static void main(String[] args) {
        String filePath = "src/main/resources/地区信息表.xls";
        List<Location> list = readOffice(filePath);
        String filePath2 = "src/main/resources/学生信息表.xlsx";
        Map map = readStandard(filePath2);

        String outfile1 = "/Users/admin/Downloads/地区信息表_ByEasyexcelCreate.xls";
        writeOffice(outfile1, list);

        String outfile2 = "/Users/admin/Downloads/学生信息表_ByEasyexcelCreate.xlsx";
        writeStandard(outfile2, map);
    }


    /**
     * 官方推荐读excel代码，默认异步读取
     *
     * @param filePath
     */
    public static List<Location> readOffice(String filePath) {
        // 写法1：
        EasyExcel.read(filePath, Location.class, new LocationListener()).sheet().doRead();


        //写法2：EasyExcel读取sheet的下标从0开始
        ExcelReader excelReader = EasyExcel.read(filePath, Location.class, new LocationListener()).build();
        ReadSheet readSheet = EasyExcel.readSheet(0).build();
        excelReader.read(readSheet);    //可以同时放入多个参数
        excelReader.finish();


        //写法3：有多个sheet，每个sheet的格式一致
        EasyExcel.read(filePath, Location.class, new LocationListener()).doReadAll();


        // 写法4：设置从哪一行开始读取。默认设置为1
        EasyExcel.read(filePath, Location.class, new LocationListener()).sheet().headRowNumber(0).doRead();


        // 写法5：同步读取数据，不实用监听器,同步读取后自动finish
        List<Location> list = EasyExcel.read(filePath).head(Location.class).sheet().doReadSync();
        for (Location data : list) {
            System.out.println(data.toString());
        }


        // 写法6：同步读取数据，不实用监听器，不实用实体类。同步读取后自动finish
        // 异步读取也可以不穿入实体，但是需要在监听器中进行处理map
        List<Map<Integer, String>> listMap = EasyExcel.read(filePath).sheet().doReadSync();
        for (Map<Integer, String> map : listMap) {
            System.out.println(map.toString());
        }
        return list;
    }


    /**
     * 读取多个sheet内容：当个excel文件中多个不同格式的sheet，可以通过下标或者名字进行选择
     *
     * @param filePath
     */
    public static Map readStandard(String filePath) {
        StudentListener studentListener = new StudentListener();
        UserListener userListener = new UserListener();

        ExcelReader excelReader = EasyExcel.read(filePath).build();
        ReadSheet readSheet1 = EasyExcel.readSheet("Sheet1").head(Student.class).registerReadListener(studentListener).build();
        ReadSheet readSheet2 = EasyExcel.readSheet(1).head(User.class).registerReadListener(userListener).build();
        excelReader.read(readSheet1, readSheet2);
        excelReader.finish();

        Map<String, Object> map = new HashMap<>();
        map.put("student", studentListener.getList());
        map.put("user", userListener.getList());
        return map;
    }

    /**
     * 官方推荐写excel代码
     *
     * @param filePath
     * @param data
     */
    public static void writeOffice(String filePath, List<Location> data) {
        // 写法1: 创建的excel的第一行，如果没有注解value，则使用字段的名字
        // 存在相同的文件，写入时默认会覆盖原来的数据
        EasyExcel.write(filePath, Location.class).sheet("模板1").doWrite(data);


        //写法2：
        ExcelWriter excelWriter = EasyExcel.write(filePath, Location.class).build();
        WriteSheet writeSheet = EasyExcel.writerSheet("模板2").build();
        excelWriter.write(data, writeSheet);
        excelWriter.finish();


        //写法3：排除自己不需要的字段
        Set<String> excludeColumnFiledNames = new HashSet<String>();
        excludeColumnFiledNames.add("province");
        EasyExcel.write(filePath, Location.class).excludeColumnFiledNames(excludeColumnFiledNames).sheet("模板3").doWrite(data);


        //写法4：只保留自己需要的字段，还可以指定该字段写在第几列
        Set<String> includeColumnFiledNames = new HashSet<String>();
        includeColumnFiledNames.add("county");
        EasyExcel.write(filePath, Location.class).includeColumnFiledNames(includeColumnFiledNames).sheet("模板4").doWrite(data);


        //写法5：复杂表头数据,根据实体类注解处理
        EasyExcel.write(filePath, ComplexHeadLocation.class).sheet("模板5").doWrite(data);


        //写法6：同一个sheet中写入多次数据
        ExcelWriter excelWriter2 = EasyExcel.write(filePath, Location.class).build();
        WriteSheet writeSheet2 = EasyExcel.writerSheet("模板6").build();
        for (int i = 0; i < 2; i++) {
            excelWriter2.write(data, writeSheet2);
        }
        excelWriter2.finish();


        //写法7：同样的数据写入不同的sheet中
        ExcelWriter excelWriter3 = EasyExcel.write(filePath, Location.class).build();
        for (int i = 0; i < 2; i++) {
            WriteSheet tmpWriteSheet = EasyExcel.writerSheet(i, "模板" + i).build();
            excelWriter3.write(data, tmpWriteSheet);
        }
        excelWriter3.finish();


        //写法8：不同的数据写入不同的sheet中
        ExcelWriter excelWriter4 = EasyExcel.write(filePath).build();
        WriteSheet writeSheet7 = EasyExcel.writerSheet(0, "模板7").head(Location.class).build();
        excelWriter4.write(data, writeSheet7);
        WriteSheet writeSheet8 = EasyExcel.writerSheet(1, "模板8").head(ComplexHeadLocation.class).build();
        excelWriter4.write(data, writeSheet8);
        excelWriter4.finish();


        //写法9: 将图片写入excel
        String imagePath = "src/main/resources/绿叶晴空.jpg";
        InputStream inputStream = null;
        try {
            List<ImageData> list = new ArrayList<>();
            ImageData imageData = new ImageData();
            imageData.setFile(new File(imagePath));
            inputStream = FileUtils.openInputStream(new File(imagePath));
            imageData.setInputStream(inputStream);
            imageData.setString(imagePath);
            imageData.setByteArray(FileUtils.readFileToByteArray(new File(imagePath)));
            imageData.setUrl(new URL("https://raw.githubusercontent.com/alibaba/easyexcel/master/src/test/resources/converter/img.jpg"));
            list.add(imageData);

            EasyExcel.write(filePath, ImageData.class).sheet().doWrite(list);
        } catch (MalformedURLException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (inputStream != null) {
                try {
                    inputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }


        //写法10：为表格设置样式；包括头样式，内容样式，其他样式等
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        headWriteCellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 10);
        headWriteCellStyle.setWriteFont(headWriteFont);

        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
        contentWriteCellStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
        WriteFont contentWriteFont = new WriteFont();
        contentWriteFont.setFontHeightInPoints((short) 10);
        contentWriteCellStyle.setWriteFont(contentWriteFont);

        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        EasyExcel.write(filePath, Location.class).registerWriteHandler(horizontalCellStyleStrategy).sheet("模板9").doWrite(data);


        //写法11：合并单元格--这里设置【每隔两行，把从第0列开始的1列数据合并】
        LoopMergeStrategy loopMergeStrategy = new LoopMergeStrategy(2, 1, 0);
        EasyExcel.write(filePath, Location.class).registerWriteHandler(loopMergeStrategy).sheet("模板10").doWrite(data);


        //写法12：使用table去写入数据，一个sheet只能有一个表头
        ExcelWriter excelWriter5 = EasyExcel.write(filePath, Location.class).build();
        WriteSheet writeSheet5 = EasyExcel.writerSheet("模板11").needHead(Boolean.FALSE).build();
        WriteTable writeTable0 = EasyExcel.writerTable(0).needHead(Boolean.TRUE).build();
        WriteTable writeTable1 = EasyExcel.writerTable(1).needHead(Boolean.TRUE).build();
        excelWriter5.write(data, writeSheet5, writeTable0);
        excelWriter5.write(data, writeSheet5, writeTable1);
        excelWriter5.finish();


        //写法13：实时生成头写入。先创建List<String>头格式的sheet仅仅写入头,然后通过table 不写入头的方式 去写入数据
        List<List<String>> list = new ArrayList<>();
        List<String> head0 = new ArrayList<String>();
        head0.add("省");
        List<String> head1 = new ArrayList<String>();
        head1.add("市");
        List<String> head2 = new ArrayList<String>();
        head2.add("县区");
        list.add(head0);
        list.add(head1);
        list.add(head2);
        EasyExcel.write(filePath).head(list).sheet("模板12").doWrite(data);


        //写法14：自动列宽。数据长度太大时不精确，而且有bug
        EasyExcel.write(filePath, Location.class).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).sheet("模板13").doWrite(data);


        //写法15:不创建对象的写数据,自己设置表头和内容
        EasyExcel.write(filePath).head(list).sheet("模板13").doWrite(data);
    }


    public static void writeStandard(String filePath, Map map) {
        List<Student> studentList = (List<Student>) map.get("student");
        List<User> userList = (List<User>) map.get("user");

        ExcelWriter excelWriter = EasyExcel.write(filePath).build();
        WriteSheet writeSheet1 = EasyExcel.writerSheet(0, "Sheet1").head(Student.class).build();
        excelWriter.write(studentList, writeSheet1);
        WriteSheet writeSheet2 = EasyExcel.writerSheet(1, "Sheet2").head(User.class).build();
        excelWriter.write(userList, writeSheet2);
        excelWriter.finish();
    }
}



